﻿using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;

namespace App_Code
{
    /// <summary>
    /// Summary description for MySQLQueryController
    /// </summary>
    public class MySQLQueryController
    {
        public string Query(string textBoxQuery)
        {
            string textBoxResults = "";

            try
            {
                using (var conn = new MySqlConnection(MySQLHelper.ConnectionString))
                {
                    string sql = textBoxQuery;

                    conn.Open();
                    var result = new List<string>();
                    using (var cmd = new MySqlCommand(sql, conn))
                    {
                        MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                        // print the collumn names

                        for (int x = 0; x < myReader.FieldCount; x++)
                        {
                            result.Add("" + myReader.GetName(x));
                            result.Add(x != myReader.FieldCount - 1 ? ", " : "\r\n");
                        }

                        while (myReader.Read())
                        {
                            for (int x = 0; x < myReader.FieldCount; x++)
                            {
                                result.Add("" + myReader.GetValue(x));
                                result.Add(x != myReader.FieldCount - 1 ? ", " : "\r\n");
                            }
                        }
                        myReader.Close();
                    }
                    conn.Close();
                    foreach (var text in result)
                    {
                        textBoxResults += text;
                    }
                    if (result.Count == 0)
                    {
                        textBoxResults += "The command execution was successful!";
                    }
                }
            }
            catch (Exception ex)
            {
                textBoxResults = "Database exception: " + ex.Message;
            }

            return textBoxResults;
        }
    }
}